package data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import logic.*;

public class JDBCDestinationDAO implements IDestinationDAO {
	
	//Tirso
	Connection c;
	JDBCProductDAO pdao;
	
	//Tirso
	public JDBCDestinationDAO() {
	       c = ConnectionManager.getInstance().checkOut();
	       pdao = new JDBCProductDAO();
	}
	
	//Tirso
	protected void finalize() {
	       ConnectionManager.getInstance().checkIn(c);
	}
	
	//Tirso
	public void addProfitsInDestination(Connection con, String idDestination, float profits) {
		PreparedStatement stmt = null;
		String sql = "UPDATE destinations SET profits = ? WHERE id = ? ";
		try{
			stmt = con.prepareStatement(sql);
			stmt.setFloat(1, profits);
			stmt.setString(2, idDestination);
			stmt.executeUpdate();
		}catch (SQLException e) {
            System.out.println("Message: addProfitsInDest: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }
        }
		
	}

	public List<Destination> selectMostPopularDestinies() {
		
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
        ResultSet result = null;
        List<Destination> lo = new LinkedList<Destination>();
        String sql = "SELECT * FROM destinations ORDER BY priority DESC LIMIT 5";

        try {
            stmt = conn.prepareStatement(sql);
            result = stmt.executeQuery();

            while (result.next()) {
            	Destination d = new Destination();
        	   
                d.setId(result.getString("id"));
                d.setNombre(result.getString("name"));
                d.setContinent(result.getString("continent"));
                d.setPriority(result.getInt("priority"));
                d.setProfits(result.getFloat("profits"));

                lo.add(d);
                
            }
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
        	ConnectionManager.getInstance().checkIn(conn);
            try {
                if (result != null) {
                    result.close();
                }

                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }

        return lo;
    }
	
	

	//Tirso
	public List<Destination> selectAll() {
		
		PreparedStatement stmt = null;
		List<Destination> ld = new LinkedList<Destination>();
		Connection con = ConnectionManager.getInstance().checkOut();
 		ResultSet result = null;
        try {
        	String sql = "SELECT * FROM destinations";
        	stmt = con.prepareStatement(sql);
            result = stmt.executeQuery();
            while(result.next()){
            	Destination dest =  new Destination();
            	dest.setId(result.getString("id"));
                dest.setNombre(result.getString("name"));
                dest.setPriority(result.getInt("priority"));
                dest.setProfits(result.getFloat("profits"));
                dest.setContinent(result.getString("continent"));
            	ld.add(dest);
            }

        } catch (SQLException e) {
            System.out.println("Message: selectAll(Dest): " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
        	ConnectionManager.getInstance().checkIn(con);
            try {
            	if (result != null){
                    result.close();
            	}
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
        return ld;
	}
	

	public float selectProfitsFromDest(Connection c, String idDest) {
		
		PreparedStatement stmt = null;
		float profits = 0;
 		ResultSet result = null;
        try {
        	String sql = "SELECT * FROM destinations WHERE id = ?";
        	stmt = c.prepareStatement(sql);
        	stmt.setString(1, idDest);
            result = stmt.executeQuery();
            result.next();
            profits = result.getFloat("profits");
            
        } catch (SQLException e) {
            System.out.println("Message: selectProfitsFromDest: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
            	if(result != null){
            		result.close();
            	}
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
        return profits;
	}


	/*@Override
	public void delete() {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void insert(Connection conn, String destinationOID, Destination d) {
		String sql = "INSERT INTO destinations ( id, name, profits, priority) VALUES (?, ?, ?, ?) ";
        PreparedStatement stmt = null;

        try {
            stmt = conn.prepareStatement(sql);

            stmt.setString(1, destinationOID);
            stmt.setString(2, d.getNombre());
            stmt.setFloat(3, d.getProfits());
            stmt.setLong(4, d.getPriority());
           

            stmt.executeUpdate();

        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }	
		
	}*/
	
	public Destination select(String idDestination){
		Connection conn = ConnectionManager.getInstance().checkOut();
		String sql = "SELECT * FROM destinations WHERE (id = ?) ";
		PreparedStatement stmt = null;
        ResultSet result = null;
        Destination d = null;
        
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, idDestination);
            result = stmt.executeQuery();
            result.next();
            
            d = new Destination();
            d.setId(result.getString("id"));
            d.setNombre(result.getString("name"));
            d.setPriority(result.getInt("priority"));
            d.setProfits(result.getFloat("profits"));
            d.setContinent(result.getString("continent"));
            
        } catch (SQLException e) {
            System.out.println("Message: selectDest(id): " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
        	ConnectionManager.getInstance().checkIn(conn);
            try {
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
        return d;
    }
	
	//Tirso
	public String selectRandomDest() {
    	PreparedStatement stmt = null;
        ResultSet result = null;
        String id = null;

        try {
            String sql = "SELECT * FROM destinations ORDER BY RAND() ";
            stmt = c.prepareStatement(sql);
            result = stmt.executeQuery();
            result.next();
            id = result.getString("id");
            
        } catch (SQLException e) {
            System.out.println("Message: selectRandomDest: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (result != null)
                    result.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }

        }
        return id;
    }
}
